星尘

51. MySQL 高级

发表于 更新于 分类于

安装

centos 7 安装 MySQL

  1. 下载所需要的安装包

  1. 检查是否安装过 MySQL

    CentOS6 执行安装命令前，先执行查询命令

1
rpm -qa|grep mysql

如果存在mysql-libs的旧版本包如下：

1
rpm -e --nodeps  mysql-libs

CentOS7 需要执行

1
rpm -qa|grep mariadb

如果存在，需要先执行卸载命令

1
rpm -e --nodeps  mariadb-libs

  1. 检查 MySQL 依赖环境

    执行安装命令前，先执行查询命令，查看是否存在所需的依赖环境，如果不存在需要到centos安装盘里进行rpm安装

1
2
rpm -qa|grep libaio
rpm -qa|grep net-tools
  1. 检查 /tmp 文件夹权限

确保有读写权限就行

  1. 进行安装

在mysql的安装文件目录下执行：（必须按照顺序执行），如在检查工作时，没有检查mysql依赖环境在安装mysql-community-server会报错

1
2
3
4
rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm
  1. 查看 MySQL 安装版本
1
mysqladmin --version
  1. 服务的初始化

为了保证数据库目录为与文件的所有者为 mysql 登陆用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化

1
mysqld --initialize --user=mysql

另外 –initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码。

查看密码

1
cat /var/log/mysqld.log

root@localhost: 后面就是初始化的密码 -2kul=pg7Z)s

  1. 服务的启动和停止

centos 7 中都使用 systemctl 来管理服务了，而 6 中是 service

1
2
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service

  1. 首次登录

首次登陆通过 mysql -uroot -p进行登录，在Enter password：录入初始化密码。

这是我们查看数据库，但是却出现了错误，提示需要重置密码

1
show databases;

因为初始化密码默认是过期的，所以查看数据库会报错，修改密码：

1
ALTER USER 'root'@'localhost' IDENTIFIED BY 'FLZX4000c';

设置完密码就可以用新密码登陆，正常使用数据库了

  1. 查看安装文件位置
1
2
3
4
5
6
--basedir /usr/bin 相关命令目录 mysqladmin mysqldump 等命令
--datadir/var/lib/mysql/ mysql 数据库文件的存放路径 
--plugin-dir/usr/lib64/mysql/pluginmysql 插件存放路径
--log-error/var/lib/mysql/jack.atguigu.errmysql 错误日志路径
--pid-file/var/run/mysqld/mysqld.pid 进程pid文件
--socket/var/lib/mysql/mysql.sock 本地连接时用的unix套接字文件   /usr/share/mysql 配置文件目录mysql脚本及配置文件 /etc/systemd/system/multi-user.target.wants/mysqld.service 服务启停相关脚本
  1. 自启动

查看mysql是否自启动（默认自启动）使用以下命令

1
systemctl list-unit-files|grep mysqld.service

如不是 enabled 可以运行如下命令设置自启动

1
systemctl enable mysqld.sercice

12 . 远程连接

确认Mysql中已经有可以通过远程登录的账户

1
select  * from mysql.user where user='li4' and host='%';

如果没有用户,先执行如下命令：

1
grant all privileges on *.* to li4@'%' identified by '123123';

修改字符集

对于中文乱码问题，就需要修改的 MySQL 的字符集。需要先修改 MySQL 的配置文件 /etc/my.cnf 加上 character_set_server=utf8 然后重新启动 MySQL。对于已经创建的库和表，也需要进行修改。

修改数据库字符集

1
alert database mydb character set 'utf8';

修改数据库表的字符集

1
alert table mytable convert to character set 'utf8';

sql_mod

创建以下表，并插入一些数据

1
2
3
4
5
6
CREATE TABLE mytbl2 (id INT,NAME VARCHAR(200),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,'zhang3',33,101);
INSERT INTO mytbl2 VALUES(2,'li4',34,101);
INSERT INTO mytbl2 VALUES(3,'wang5',34,102);
INSERT INTO mytbl2 VALUES(4,'zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);

现在需要查询每个机构年龄最大的人

1
SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;

上面查询 SQL 看似没有问题，但其实会查询报错。这是因为在 MySQL 5.7 中，group by 使用的原则是 select 后面只能放 函数 和 group by 后的字段 (即 sql_mod 中的 ONLY_FULL_GROUP_BY，group 全覆盖)，上面 SQL 中的 NAME 字段是不允许的。所以查询会报错，但是在 MySQL 5.5 中运行时没有问题的，原因是 5.5 的校验没有那么严格，但是 5.5 虽然能查询出来，但是数据是错误的。

正确的查询 SQL 如下：

1
2
3
SELECT * FROM mytbl2 m INNER JOIN(
	SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.dept AND m.age=ab.maxage;

MySQL的sql_mode合理设置
sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

查看 sql_mode

1
show  variables like 'sql_mode';

设置 sql_mode

1
set sql_mode='ONLY_FULL_GROUP_BY';

sql_mode常用值如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：
在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

ORACLE：
设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

逻辑架构

底层架构原理

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，
插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

2.1 Management Serveices & Utilities： 系统管理和控制工具
2.2 SQL Interface: SQL接口，接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器，SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。 用一个例子就可以理解： select uid,name from user where gender= 1; 优化器来决定先投影还是先过滤。
2.5 Cache和Buffer： 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

show profile 查看 SQL 声明周期

如果需要使用 show profile 查看 SQL 声明周期，那么需要修改配置文件 /etc/my.cnf，在最后新增一行，然后重启mysql 

1
query_cache_type=1

查看是否已经开启了 profile。 

1
show variables  like '%profiling%';

如果没有开启，执行以下命令进行开启

1
set profiling=1;

执行查询语句

1
SELECT * FROM mytbl2 WHERE NAME = "zhang3";

显示最近的几次查询

1
show profiles;

查看程序的执行步骤

1
show profile cpu,block io for query 编号

最后观察执行的步骤

再次执行相同的查询，同样也进行 profile 信息的查看，会发现步骤比第一次少了很多，时间上也快了很多

存储引擎

查看所有的存储引擎

查看所有的存储引擎

1
show engines;

查看你的mysql当前默认的存储引擎:

1
show variables like '%storage_engine%';

MyISAM 和 InnoDB 的区别

对比项 MyIsAM InnoDB
外键 不支持 支持
事务 不支持 支持
行表锁 表锁。即使操作一条记录也会锁住整个表，不适合高并发。因为锁的范围太大了，所以不会出现死锁的情况。 行锁，操作的时候只锁住某一行，不对其他行有影响，适合高并发的情况，但是可能会出现死锁的情况
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还缓存真实数据。对内存要求较高，而且内存大小对性能有决定性作用。
关注点 节省资源，消耗少，简单业务 并发写，事务，更大资源
默认安装 默认会安装 默认会安装

那么那些地方用到了 MyISAM 呢？其实 MySQL 的系统表，所用的存储引擎就是 MyISAM

InnoDB 存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

MyISAM 存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

Archive 引擎
Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

Blackhole 引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

CSV 引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

Memory 引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。

Federated 引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

关联查询

创建部门表

1
2
CREATE TABLE `t_dept` (`id` INT(11) NOT NULL AUTO_INCREMENT,`deptName` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建员工表

1
2
3
4
5
6
7
8
9
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno INT NOT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_dept_id` ( `deptId` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO t_dept(deptName,address)  VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);

查询所有有门派的人员信息 （ A、B两表共有）内连接

1
select * from t_dept as dept inner join t_emp as emp on dept.id = emp.deptId

列出所有用户，并显示其机构信息 （A的全集）左外连接

1
select * from  t_emp as emp left join t_dept as dept on dept.id = emp.deptId

所有不入门派的人员 （A的独有）

1
select * from  t_emp as emp left join t_dept as dept on dept.id = emp.deptId WHERE dept.id is null

所有没人入的门派 （B的独有）

1
select * from   t_dept as dept left join t_emp as emp  on dept.id = emp.deptId WHERE emp.id is null

列出所有人员和机构的对照关系(AB全有=A的全集 + B的独有)【因为 MySQL 不支持 Full Join，所以使用 UNION 来实现】
UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
在使用 UNION 的时候一定要注意，前后的字段顺序一定要对上，否则查询出来的数据可能会错位 (下面 SQL 中的 emp.*,dept.* 就是为了让字段顺序保持一致)

1
2
3
select emp.*,dept.* from  t_emp as emp left join t_dept as dept on dept.id = emp.deptId
UNION all
select emp.*,dept.* from   t_dept as dept left join t_emp as emp  on dept.id = emp.deptId WHERE emp.id is null

列出所有没入派的人员和没人入的门派（A的独有+B的独有）

1
2
3
select emp.*,dept.* from  t_emp as emp left join t_dept as dept on dept.id = emp.deptId WHERE dept.id is null
UNION all
select emp.*,dept.* from   t_dept as dept left join t_emp as emp  on dept.id = emp.deptId WHERE emp.id is null

新增掌门人字段

1
ALTER TABLE `t_dept` add CEO INT(11);
1
2
3
4
5
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;

求各个门派对应的掌门人名称

1
SELECT * from t_emp as emp inner join t_dept as dept WHERE dept.CEO = emp.id

求所有人物对应的掌门名称

写法1

1
2
3
select a.name as ceoname, ab.name FROM
(SELECT a.name, b.CEO from t_emp as a left join t_dept as b on a.deptId = b.id) ab 
left JOIN t_emp as a on a.id = ab.CEO

写法2

1
SELECT a.name, (select c.name from t_emp as c WHERE c.id = b.CEO) as ceoname from t_emp as a left join t_dept as b on a.deptId = b.id

索引

优缺点

优点：类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

B+Tree与B-Tree 的区别

　1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
　 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。
　
思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？
1) B+树的磁盘读写代价更低
　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2) B+树的查询效率更加稳定
　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

分类及创建

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

1
CREATE index idx_name on t_emp(name)

查看索引信息

1
show index from t_emp

唯一索引

索引列的值必须唯一，但允许有空值

1
CREATE UNIQUE INDEX idx_empno ON t_emp(empno)

查看索引信息

1
show index from t_emp

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

复合索引

即一个索引包含多个列

1
CREATE INDEX idx_age_deptId ON t_emp(age, deptId)

查看索引信息

1
show index from t_emp

什么时候需要创建索引

主键自动建立唯一索引。
频繁作为查询条件的字段应该创建索引。
查询中与其它表关联的字段，外键关系建立索引。
单键/组合索引的选择问题， 组合索引性价比更高。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段。

什么时候不需要创建索引

经常增删改的表或者字段 (提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件)。
Where条件里用不到的字段不创建索引。
过滤性不好的不适合建索引 (例如，性别字段，因为值的可能性就那么几种)。

Explain

使用 Explain 关键字可以模拟优化器（优化器是在不影响 SQL 执行结果的前提下，改变 SQL 的顺序，从而提高 效率）执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

执行以下 explain

1
explain  SELECT a.name, (select c.name from t_emp as c WHERE c.id = b.CEO) as ceoname from t_emp as a left join t_dept as b on a.deptId = b.id

字段名称解释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
id: 【重要】每个 id 表示一趟独立的查询，一个 sql 的查询趟数越少越好。id 相同时，执行顺序由上至下。id 不同时（子查询，id的序号会递增），id值越大优先级越高，越先被执行。【例如上图中，就执行了2趟查询，其中 id 为 2 的会先执行，然后在从上到下的执行 id 为 1 的（这一趟执行两次查询，先查 a 再查 b）】

select_type:  查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

table: 显示这一行的数据是关于哪张表的

partitions: 代表分区表中的命中情况，非分区表，该项为null

type:【重要】显示查询使用了何种类型, 从最好到最差依次是 system > const > eq_ref > ref > range> index > ALL。range 一般就是在你的 where 语句中出现了 between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好。index 一般是表有索引，但是 where 后面的条件没有使用索引。ALL 就是全表扫描。要尽量保证 type 要在 range 或者 range 之上，不能为 index 或者 all。

possible_keys: 显示可能应用到的索引，一个或多个。

key: 显示实际用到的索引。

key_len: 【重要】表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。该值越大越好！

ref: 显示索引的哪一列被使用了

rows: 【重要】rows 列显示 MySQL 认为它执行查询时必须检查的行数。该值越小越好！

Extra: 【重要】值有很多中，但是这里列举其中不好的三种，使用时，要尽量避免以下三种情况的出现。Using filesort - order by 查询没有用上索引就会出现。Using temporary - group by 查询没有用上索引就会出现。using join buffer - join 查询没有用上索引就会出现。

单表查询优化

  1. 全值匹配：where 后面的字段都建立了索引

  2. 最佳左前缀法则：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

  3. 在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

  4. 范围条件后面的字段索引都不生效了
  5. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  6. is not null 无法使用索引,但是is null是可以使用索引的
  7. like 以通配符开头(‘%abc…’) mysql 索引失效会变成全表扫描的操作。但是 like(‘abc%’) 不会
  8. 字符串不加单引号，或者进行类型转换，那么会引起索引失效导致全表扫描。

例子，假设建立了索引 index(a,b,c) 下列哪些情况未使用索引，为什么

where 条件 索引是否被使用 原因
where a=3 是，使用到 a
where a=3, b=4 是，使用到 a, b
where a=3, b=4, c=5 是，使用到 a, b, c
where b=4 或 where b=3 and c=4 或 where c=4 否 不满足最佳左前缀法则，索引顺序是 abc
where a=3 and c=5 是，使用到 a b 中间中断了，不满足最佳左前缀法则
where a=3 and b> 4 and c=5 是，使用到了 a，b c不满足，因为范围条件后面的字段索引都不生效了
where a is null and b is not null 是，使用到了 a is not null 无法使用索引,但是 is null是可以使用索引
where a <> 3 否 使用不等于(!= 或者<>)的时候无法使用索引
where abs(a) = 3 否 在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效
where a=3, b like ‘kk%’ and c=4 是，使用到了 abc
where a=3, b like ‘%kk’ and c=4 是，使用到了 a like 以通配符开头(‘%abc…’) 索引会失效，导致 b 中间中断了 c 也不满足索引使用
where a=3, b like ‘%kk%’ and c=4 是，使用到了 a 理由同上
where a=3, b like ‘k%kk%’ and c=4 是，使用到了 abc

单表查询索引建立建议

  1. 对于单键索引，尽量选择针对当前query过滤性更好的索引（例如建议将身份证，手机号等字段建立索引而不是性别，家庭住址等）
  2. 在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。（建立索引的时候，过滤条件好的放在前面）
  3. 在选择组合索引的时候，尽量选择可以能够包含当前 query 中的 where 字句中更多字段的索引
  4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面（因为条件后面的索引会失效）

关联查询优化

什么是驱动表和被驱动表: 在下面 SQL 中 class 表是驱动表，book 表是被驱动表。

1
SELECT * FROM class LEFT JOIN book ON class.card = book.card;
  1. 驱动表的字段就算建立了索引，在关联查询的情况下也无法避免全表扫描。只有被驱动表的 join 字段被索引，此时关联查询的被驱动表的索引才会生效。也就是说需要保证被驱动表的 join 字段已经被索引。
  2. left join 时，尽量选择小表作为驱动表，大表作为被驱动表。这样能减少扫描
  3. inner join 时，mysql会自己帮你把小结果集的表选为驱动表。(如果 MySQL 选择错误，可以使用 STRAIGHT_JOIN 指定关联关系，放在前面的是驱动表，后面是被驱动表。STRAIGHT_JOIN 性能优化)
  4. 子查询尽量不要放在被驱动表。这是因为关联查询只有被驱动表建立的索引会生效，而如果将子查询放在了被驱动表，因为子查询会生成临时的虚拟表，并且是没有索引的，这就会导致全表扫描生成的生成临时虚拟表。再加上驱动表也被全表扫描，所以情况更坏。
  5. 能够直接多表关联的尽量直接关联，不用子查询。这是因为子查询会生成临时的虚拟表，并且是没有索引的。

子查询优化

尽量不要使用 not in 或者 not exists。应该尽量使用关联关系进行代替。

排序分组优化

在使用 order by 的时候，以下情况不会使用索引：

  1. 无过滤 不索引。如果没有过滤条件，那么一定不会使用索引（以下查询假设建立了 index(age, deptid) 索引)
1
2
3
explain  select SQL_NO_CACHE * from emp order by age,deptid;  # 无过滤条件，不走索引
explain  select SQL_NO_CACHE * from emp order by age,deptid limit 10;  # 有过滤条件，走索引
explain  select SQL_NO_CACHE * from emp where age > 10 order by age,deptid;  # 有过滤条件，走索引
  1. 顺序错 ，必排序。如果字段的顺序和建立索引时的顺序不一样，那么会发生 Using filesort 也就是没有用到索引 (以下查询假设建立了 index(age, deptid, name) 索引)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
explain select * from emp where age=45 order by deptid;  
# 有过滤条件且按照索引顺序，先 age 后deptid，所以走索引

explain select * from emp where age=45 order by deptid,name;  
# 有过滤条件且按照索引顺序，先age 后 deptid 最后 name，所以走索引

explain select * from emp where age=45 order by deptid,empno; 
# 有过滤条件但是按照索引顺序，不走索引

explain select * from emp where age=45 order by name,deptid; 
# 有过滤条件但是按照索引顺序，不走索引

explain select * from emp where deptid=45 order by age; 
# 有过滤条件但是按照索引顺序，不走索引
  1. 方向反，必排序。若排序的方向相反，则也会发 Using filesort 也就是没有用到索引 (以下查询假设建立了 index(age, deptid, name) 索引)
1
2
3
4
5
explain select * from emp where age=45 order by deptid desc, name desc;
# 有过滤条件且按照索引顺序且排序方向相同，所以走索引

explain select * from emp where age=45 order by deptid asc, name desc;
# 有过滤条件且按照索引顺序但排序方向相反，所以不走索引

在使用 group by 的时候，以下情况不会使用索引：

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

慢查询日志

慢查询日志默认是关闭的。MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

查看慢查询是否开启

1
SHOW VARIABLES LIKE '%slow_query_log%';

开启慢查询

1
set global slow_query_log=1;

查看当前多少秒算慢

1
SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阙值时间

1
set  long_query_time=1

收集到日志后，后续可以使用 mysqldumpslow 工具进行慢查询日志分析

主从复制

slave 会从 master 读取 binlog 来进行数据同步

MySQL 复制过程

  1. master 将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
  2. slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）
  3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化的

复制的基本原则

  1. 每个 slave 只有一个 master
  2. 每个 slave 只能有一个唯一的服务器 ID
  3. 每个 master 可以有多个 salve

binlog 的三种格式

  1. statement：基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
  2. row：基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在binlog中会生成很多条语句，可能导致从库延迟变大。
  3. mixed：混合模式，根据语句来自动选择选用是 statement 还是 row 模式。

补充注意事项

null和任何值都不能比较，null只能用is null 或者is not null 来判断，不能用=或者！=来判断